package com.boss.salary.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IAgencySalaryMultipleDao;
import com.boss.salary.entity.AgencySalary;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.entity.SalaryHistory;
import com.boss.salary.entity.SalaryMonthEnd;
import com.boss.salary.vo.AgencySalaryVO;
import com.boss.salary.vo.SalaryMonthEndVO;
import com.boss.salary.vo.UnitSalaryReportVO;
import com.boss.sys.ca.user.dto.UserDTO;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

@Repository
public class AgencySalaryMultipleDaoImpl extends BaseDaoImpl implements IAgencySalaryMultipleDao {
    private static DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    private static final int IS_THIRTEEN = 1; //是十三月工资
    private static final int NO_THIRTEEN = 0; //不是十三月工资

    @Override
    public List<AgencySalary> queryAgencySalaryPage(PaginationDTO paginationDTO) {
        StringBuilder sql = new StringBuilder();

        sql.append("select id, create_time, update_time, agency_id, salary_id")
                .append(" is_accsalwithhold, column_name, is_deleted from ").append(TableName.AGENCY_SALARY);
        sql.append(" where  is_deleted=0 ");
        sql.append(" order by create_time");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(null, paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper<>(AgencySalary.class), params);
    }

    @Override
    public int queryAgencySalaryTotal() {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) from ").append(TableName.AGENCY_SALARY);
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void saveAgencySalary(AgencySalary agencySalary) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.AGENCY_SALARY);
        sql.append("(id, create_time, update_time, salary_id, agency_id, is_accsalwithhold, column_name, is_deleted, mof_div_code) ");
        sql.append("values (?,?,?,?,?,?,?,?,?)");
        Object[] params = new Object[]{
                agencySalary.getId(), agencySalary.getCreateTime(), agencySalary.getUpdateTime(),
                agencySalary.getSalaryId(), agencySalary.getAgencyId(), agencySalary.getIsAccsalwithhold(),
                agencySalary.getColumnName(), agencySalary.getIsDeleted(),agencySalary.getMofDivCode()
        };
        this.getJdbcTemplate().update(sql.toString(), params);

    }

    @Override
    public void updateAgencySalary(AgencySalary agencySalary) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_SALARY);
        sql.append(" set update_time=?, salary_id=?, agency_id=?, is_accsalwithhold=?,")
                .append(" column_name=?, is_deleted=? where id=?");
        Object[] params = new Object[]{
                agencySalary.getUpdateTime(), agencySalary.getSalaryId(), agencySalary.getAgencyId(),
                agencySalary.getIsAccsalwithhold(), agencySalary.getColumnName(), agencySalary.getIsDeleted(),
                agencySalary.getId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deleteAgencySalary(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_SALARY);
        sql.append(" set is_deleted='1' ")
                .append("where id=?");
        Object[] params = new Object[]{
                id
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public List<AgencySalary> queryAgencySalaryInfo(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();

        sql.append("select id, create_time, update_time, agency_id, salary_id, ")
                .append(" is_accsalwithhold, column_name, is_deleted from ").append(TableName.AGENCY_SALARY);
        sql.append(" where is_deleted = '0'");
        if (queryParam.size() > 0) {
            // 单位
            if (!StringUtils.isEmpty(queryParam.get("agencyId"))) {
                String agencyId = queryParam.get("agencyId").toString();
                sql.append(" and agency_id = ? ");
                paramList.add(agencyId);
            }
            //工资
            if (!StringUtils.isEmpty(queryParam.get("salaryId"))) {
                String salaryId = queryParam.get("salaryId").toString();
                sql.append(" and salary_id = ? ");
                paramList.add(salaryId);
            }
            //区划
            if (!StringUtils.isEmpty(queryParam.get("province"))) {
                sql.append(" and mof_div_code = ? ");
                paramList.add(queryParam.get("province"));
            }
        }
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencySalary.class), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> selectUnitSalaryReportPage(PaginationDTO paginationDTO,
                                                                String agencyId,
                                                                List<String> salaryIds,
                                                                String salaryMonth,
                                                                Map<String, String> currentSalaryIdJoinColumnMap,
                                                                Map<String, Object> condition,
                                                                String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append("id \"id\",is_thirteen \"is_thirteen\",updateTime \"updateTime\",personId \"personId\",agencyId \"agencyId\",meritPayCode \"meritPayCode\",currentMonth \"currentMonth\",currentMonth_history \"currentMonth_history\"")
                .append(",deptId \"deptId\",personName \"personName\",idenNo \"idenNo\",existsFile \"existsFile\",personnelCode \"personnelCode\",");
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder currentStr;
        StringBuilder lastStr;
        if (!CollectionUtils.isEmpty(salaryIds)) {
            currentStr = new StringBuilder();
            lastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
           // StringBuilder differentStr = new StringBuilder(" case when  s2 = 0 or "); s2报错!
            StringBuilder differentStr = new StringBuilder(" case when   ");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
               // String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(currentColumn).append(" \"").append(currentColumn).append("\", ");
                lastStr.append(currentColumn).append("_history \"").append(currentColumn).append("_history\",");
                ifNullCurrentStr.append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn).append(" , ");
               // if (!StringUtils.isEmpty(lastColumn)) {
                ifNullLastStr.append("  0  ").append(currentColumn).append("_history, ");
               // } else {
//                    如果上月没有对应的工资类型，则工资默认为零
                 //   ifNullLastStr.append(" 0 ").append(currentColumn).append("_history, ");
               // }
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                    differentStr.append(" then 1 else 0 end \"different\" ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id,smed.is_thirteen,smed.update_time updateTime, pet.person_id personId, pet.agency_id agencyId, pet.merit_pay_code meritPayCode, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        sb.append(" ? currentMonth_history, ");
        paramList.add(salaryMonth);
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" pet.dept_id deptId, pet.person_name personName, pet.iden_no idenNo, pet.show_order showOrder, pet.personnel_code personnelCode,");
        sb.append(" case when  fio.middle_id is not null and fio.middle_id != '' then 1 else  0 end existsFile ");
        sb.append(" from ")
                .append(TableName.PERSON_EXT).append(" pet ");
        //left join
        sb.append("  join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id  ");
        sb.append(" and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(" ( select distinct middle_id from ").append(TableName.SAL_FILE_INFO)
                .append(" where ").append(" is_deleted = 0 and agency_id = ? ")
                .append(" and middle_table_code = '2' and salary_month = ? ) fio ")
                .append(" on ").append(" fio.middle_id = pet.person_id ");
        paramList.add(agencyId);
        paramList.add(salaryMonth);
        sb.append(" where ").append(" pet.is_deleted = ?  "); // 过滤掉已删除的用户
        paramList.add(SystemDictConstant.IS_FALSE);
        if(!StringUtils.isEmpty(mofDivCode)){
            sb.append(" and pet.mof_div_code = ? ");
            paramList.add(mofDivCode);
        }
        sb.append(" AND pet.agency_id = ? ");
        paramList.add(agencyId);
        concatUnitAuditPageConditionSql(sb, paramList, condition);
        //concatUnitAuditTotalConditionSql(condition, paramList, sb);
        sb.append("  ) t order by \"different\" desc, deptId,-showOrder desc  ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
            return super.getJdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int selectSalaryChangePersonNumOnSubmit(String agencyId, List<String> salaryIds,
                                           String salaryMonth,
                                           String lastSalaryMonth,
                                           Map<String, String> currentSalaryIdJoinColumnMap,
                                           Map<String, String> lastSalaryIdJoinColumnMap,
                                           Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select count(different) from ( ");
        sb.append(" select ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            StringBuilder lastStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            ifNullLastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            StringBuilder differentStr = new StringBuilder(" case when (");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
 //               String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(currentColumn).append(", ");
                lastStr.append(currentColumn).append("_history, ");
                ifNullCurrentStr.append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn).append(" , ");
//                if (!StringUtils.isEmpty(lastColumn)) {
                    ifNullLastStr.append("  0  ").append(currentColumn).append("_history, ");
//                } else {
//                    如果上月没有对应的工资类型，则工资默认为零
//                    ifNullLastStr.append(" 0 ").append(currentColumn).append("_history, ");
//                }
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                    differentStr.append(" )then 1 else null end different ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" pet.person_id ");
        sb.append(" from ")
                .append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" left join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append("  pet.agency_id=smed.agency_id and pet.is_thirteen=smed.is_thirteen")
//                .append(" left join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
//                .append(" on ").append(" pet.person_id = smbn.person_id and pet.agency_id=smbn.agency_id  ")
                .append(" where ")
                .append("  pet.is_stop_salary = ? ") // 过滤掉已停发的用户
                .append(" and pet.agency_id = ? ");
//        paramList.add(lastSalaryMonth);
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(agencyId);
        if (!CollectionUtils.isEmpty(queryParam)) {
            Object salaryType = queryParam.get("salaryType");
            if (!StringUtils.isEmpty(salaryType)) {
                sb.append(" and pet.merit_pay_code = ? ");
                paramList.add(String.valueOf(salaryType));
            }
        }

        sb.append(" ) t) d");
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), Integer.class);
    }

    @Override
    public int selectSalaryChangePersonNum(String agencyId, List<String> salaryIds,
                                           String salaryMonth,
                                           String lastSalaryMonth,
                                           Map<String, String> currentSalaryIdJoinColumnMap,
                                           Map<String, String> lastSalaryIdJoinColumnMap,
                                           Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select sum(different) from ( ");
        sb.append(" select ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            StringBuilder lastStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            ifNullLastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            StringBuilder differentStr = new StringBuilder(" case when ");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                //               String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(currentColumn).append(", ");
                lastStr.append(currentColumn).append("_history, ");
                ifNullCurrentStr.append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn).append(" , ");
//                if (!StringUtils.isEmpty(lastColumn)) {
                ifNullLastStr.append(" nvl( smbn.").append(currentColumn).append(", 0 ) ").append(currentColumn).append("_history, ");
//                } else {
//                    如果上月没有对应的工资类型，则工资默认为零
//                    ifNullLastStr.append(" 0 ").append(currentColumn).append("_history, ");
//                }
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                    differentStr.append(" then 1 else 0 end  different ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" pet.person_id ");
        sb.append(" from ")
                .append(TableName.PERSON_EXT).append(" pet ")
                .append(" left join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id and pet.agency_id=smed.agency_id ")
                .append(" left join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pet.person_id = smbn.person_id and pet.agency_id=smbn.agency_id  ")
                .append(" where ")
                .append("  pet.is_deleted  = 0 ")
                .append(" and pet.is_stop_salary = ? ") // 过滤掉已停发的用户
                .append(" and pet.agency_id = ? ");
//        paramList.add(lastSalaryMonth);
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(agencyId);
        if (!CollectionUtils.isEmpty(queryParam)) {
            Object salaryType = queryParam.get("salaryType");
            if (!StringUtils.isEmpty(salaryType)) {
                sb.append(" and pet.merit_pay_code = ? ");
                paramList.add(String.valueOf(salaryType));
            }
        }

        sb.append(" ) t) d");
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), Integer.class);
    }

    @Override
    public List<Map<String, Object>> selectUnitAuditPage(PaginationDTO paginationDTO,
                                                         String agencyId,
                                                         Integer isThirteen,
                                                         List<String> salaryIds,
                                                         String salaryMonth,
                                                         Map<String, String> currentSalaryIdJoinColumnMap,
                                                         Map<String, Object> condition,String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append("id \"id\",updateTime \"updateTime\",personId \"personId\",agencyId \"agencyId\",currentMonth \"currentMonth\",currentMonth_history \"currentMonth_history\",deptId \"deptId\",personName \"personName\",idenNo \"idenNo\",isThirteen \"isThirteen\" ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            StringBuilder lastStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            ifNullLastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            StringBuilder differentStr = new StringBuilder(" , case when  ");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                //String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(" , ").append(currentColumn).append(" \"").append(currentColumn).append("\"");
                lastStr.append(" , ").append(currentColumn).append("_history ").append(" \"").append(currentColumn).append("_history\" ");
                ifNullCurrentStr.append(" , ").append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn);
                //if (!StringUtils.isEmpty(lastColumn)) {
                    ifNullLastStr.append(" , ").append(" nvl( smbn.").append(currentColumn).append(", 0 ) ").append(currentColumn).append("_history ");
                //} else {
//                    如果上月没有对应的工资类型，则工资默认为零
                  //  ifNullLastStr.append(" , 0 ").append(currentColumn).append("_history ");
               // }
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                    differentStr.append(" then 1 else 0 end different ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pet.person_id personId, pet.agency_id agencyId,smed.is_thirteen isThirteen,")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        sb.append(" ? currentMonth_history,  pet.dept_id deptId, pet.person_name personName, pet.iden_no idenNo ");
        paramList.add(salaryMonth);
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" from ")
                .append(TableName.PERSON_EXT).append(" pet ");
        sb.append(" left join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pet.person_id= smed.person_id  and smed.agency_id = ? ");
        paramList.add(agencyId);
                sb.append(" left join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pet.person_id = smbn.person_id  and smbn.agency_id = ? ");
        paramList.add(agencyId);
        // 过滤掉已删除的用户
        sb.append(" where ").append(" pet.is_deleted = 0 ");
        if(!StringUtils.isEmpty(mofDivCode)){
            sb.append(" and pet.mof_div_code= ?");
            paramList.add(mofDivCode);
        }
        sb.append("   and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" and smed.is_thirteen = ? ");
        paramList.add(isThirteen);
        concatUnitAuditPageConditionSql(sb, paramList, condition);
//        拼接第十三月工资
//        sb.append(" union all ");
//        concatThirteenSalary(sb, paramList, agencyId, salaryIds,
//                currentSalaryIdJoinColumnMap, condition,mofDivCode);

        sb.append(" ) t order by isThirteen, different desc, updateTime desc ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().queryForList(querySql, params);
    }

    private void concatThirteenSalary(StringBuilder sb,
                                      List<Object> paramList,
                                      String agencyId,
                                      List<String> salaryIds,
                                      Map<String, String> currentSalaryIdJoinColumnMap,
                                      Map<String, Object> condition,String mofDivCode){
        sb.append(" select ")
                .append(" smed.id, smed.update_time \"updateTime\", pio.id \"personId\", pet.agency_id \"agencyId\", ")
                .append(" '13月' \"currentMonth\", '' \"currentMonth_history\", nvl(smed.is_thirteen, 0) \"isThirteen\", ")
                .append(" pet.dept_id \"deptId\", pio.person_name \"personName\", pio.iden_no \"idenNo\" ");
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        for(String salaryId : salaryIds){
            String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
            ifNullCurrentStr.append(" , nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn);
            ifNullLastStr.append(" , 0 ").append(currentColumn).append("_history ");
        }
        sb.append(ifNullCurrentStr).append(ifNullLastStr);
        sb.append(" from ")
                .append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" on ").append(" pio.id = smed.person_id and pio.is_deleted = 0 ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" where ").append(" smed.agency_id = ? and smed.is_thirteen = 1 ");
        paramList.add(agencyId);
        if(!StringUtils.isEmpty(mofDivCode)){
            sb.append(" and pio.mof_div_code= ?");
            paramList.add(mofDivCode);
        }
        concatUnitAuditPageConditionSql(sb, paramList, condition);
    }

    private void concatUnitAuditPageConditionSql(StringBuilder sb, List<Object> paramList, Map<String, Object> condition) {
        if (!CollectionUtils.isEmpty(condition)) {
            Object state = condition.get("state");
            if (!StringUtils.isEmpty(state)) {
                sb.append(" and pet.state = ? ");
                paramList.add(String.valueOf(state));
            }
            Object personName = condition.get("personName");
            if (!StringUtils.isEmpty(personName)) {
                sb.append(" and pet.person_name like ? ");
                paramList.add('%'+condition.get("personName").toString()+'%');
                //paramList.add(String.valueOf(personName));
            }

            Object idenNo = condition.get("idenNo");
            if (!StringUtils.isEmpty(idenNo)) {
                sb.append(" and pet.iden_no like ? ");
                paramList.add('%'+ condition.get("idenNo").toString()+'%');
               // paramList.add(String.valueOf("'%"+idenNo+"%'"));
            }
            Object salaryType = condition.get("salaryType");
            if (!StringUtils.isEmpty(salaryType)) {
                sb.append(" and pet.merit_pay_code = ?");
                paramList.add(String.valueOf(salaryType));
            }
            Object deptId = condition.get("deptId");
            if (!StringUtils.isEmpty(deptId)) {
                sb.append(" and pet.dept_id = ?");
                paramList.add(String.valueOf(deptId));
            }
            Object personnelCode = condition.get("personnelCode");
            if (!StringUtils.isEmpty(personnelCode)) {
                sb.append(" and pet.personnel_code like ? ");
                paramList.add('%'+ condition.get("personnelCode").toString()+'%');
               // paramList.add(String.valueOf("'%"+personnelCode+"%'"));
            }

            //按工资项过滤金额
            Object salaryColum = condition.get("salaryColum");
            Object salaryColumMin = condition.get("salaryColumMin");
            Object salaryColumMax = condition.get("salaryColumMax");
            if (!StringUtils.isEmpty(salaryColum)&&(!StringUtils.isEmpty(salaryColumMax)||!StringUtils.isEmpty(salaryColumMin))) {

                if (!StringUtils.isEmpty(salaryColumMax)) {//小于等于最大值
                    sb.append(" and smed.").append(salaryColum);
                    sb.append(" <= ?");
                    paramList.add(String.valueOf(salaryColumMax));
                }
                if (!StringUtils.isEmpty(salaryColumMin)) {//大于等于最小值
                    sb.append(" and smed.").append(salaryColum);
                    sb.append(" >= ?");
                    paramList.add(String.valueOf(salaryColumMin));
                }
            }
        }
    }

    @Override
    public SalaryHistory selectSalaryHistoryDataByPersonId(String agencyId,
                                                           String personId,
                                                           String lastSalaryMonth,
                                                           List<String> queryColumn) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        StringBuilder sb = new StringBuilder();
        sb.append(" select id");
        for (String name : queryColumn) {
            sb.append(",").append(" nvl(").append(name).append(", 0) ").append(name);
        }
        sb.append(" from ")
                .append(TableName.SALARY_MONTH_BEGIN)
                .append(" where ")
                .append(" agency_id = ? ")
                .append(" and person_id = ? ");
              //  .append(" and salary_month = ? ");
        try {
            return super.getJdbcTemplate().queryForObject(
                    sb.toString(), new BeanPropertyRowMapper<>(SalaryHistory.class), agencyId, personId);
        } catch (EmptyResultDataAccessException e) {
//            查询结果为空会报这个错，所以在报错的时候catch并返回null
            SalaryHistory salaryHistory = new SalaryHistory();
            Class<?> clazz = salaryHistory.getClass();
            for (String name : queryColumn) {
                Method setMethod = clazz.getMethod("set" + name.substring(0, 1).toUpperCase() + name.substring(1), Double.class);
                setMethod.invoke(salaryHistory, 0.00);
            }
            return salaryHistory;
        }
    }

    @Override
    public int selectUnitSalaryReportTotal(String agencyId, Map<String, Object> condition,String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select count(*) ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" AND pet.is_stop_salary = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" AND pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id ");
        sb.append(" and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" where ").append(" pio.is_deleted = ? "); // 过滤掉已删除的用户
        paramList.add(SystemDictConstant.IS_FALSE);
        if(!StringUtils.isEmpty(mofDivCode)){
            sb.append(" and pio.mof_div_code = ?");
            paramList.add(mofDivCode);
        }

        concatUnitAuditPageConditionSql(sb, paramList, condition);
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    @Override
    public int selectUnitAuditTotal(String agencyId, Map<String, Object> condition,String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ").append(" count(*) ")
                .append(" from (")
                .append(" select pio.id ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        // 过滤掉已删除的用户
        sb.append(" where ").append(" pio.is_deleted = 0 ");
        if(!StringUtils.isEmpty(mofDivCode)){
            sb.append(" and pio.mof_div_code=?");
            paramList.add(mofDivCode);
        }
        concatUnitAuditTotalConditionSql(condition, paramList, sb);

        sb.append(" ) t ");
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    private void concatUnitAuditTotalConditionSql(Map<String, Object> condition, List<Object> paramList, StringBuilder sb) {
        if (!CollectionUtils.isEmpty(condition)) {
            Object personId = condition.get("personId");
            if(!StringUtils.isEmpty(personId)){
                sb.append(" and pet.person_id = ? ");
                paramList.add(String.valueOf(personId));
            }

            Object state = condition.get("state");
            if (!StringUtils.isEmpty(state)) {
                sb.append(" and pet.state = ? ");
                paramList.add(String.valueOf(state));
            }
            Object personName = condition.get("personName");
            if (!StringUtils.isEmpty(personName)) {
                sb.append(" and pet.person_name like ?");
                paramList.add('%'+ condition.get("personName").toString()+'%');
                //paramList.add(String.valueOf(personName));
            }

            Object idenNo = condition.get("idenNo");
            if (!StringUtils.isEmpty(idenNo)) {
                sb.append(" and pet.iden_no like ? ");
                paramList.add('%'+ condition.get("idenNo").toString()+'%');
              //  paramList.add(String.valueOf(idenNo));
            }
            Object salaryType = condition.get("salaryType");
            if (!StringUtils.isEmpty(salaryType)) {
                sb.append(" and pet.merit_pay_code = ?");
                paramList.add(String.valueOf(salaryType));
            }
            Object deptId = condition.get("deptId");
            if (!StringUtils.isEmpty(deptId)) {
                sb.append(" and pet.dept_id = ?");
                paramList.add(String.valueOf(deptId));
            }


        }
    }

    @Override
    public Map<String, Object> selectUnitSalarySubmitReportSum(String agencyId, List<String> salaryIds, String salaryMonth, Map<String, String> currentSalaryIdJoinColumnMap, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select '合计' \"personName\" ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(" , sum(").append(currentColumn).append(") ").append(currentColumn);
                ifNullCurrentStr.append(" , ").append(" sum(nvl( smed.").append(currentColumn).append(", 0 )) ").append(currentColumn);
            }
            sb.append(currentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time \"updateTime\", pio.id \"personId\", pet.agency_id \"agencyId\", ")
                .append(" ? \"currentMonth\", ");
        paramList.add(salaryMonth);
        sb.append(" nvl(smed.is_thirteen, 0) \"isThirteen\", pet.dept_id \"deptId\", pio.person_name \"personName\", pio.iden_no \"idenNo\" ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr);
        }
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ");
        sb.append(" left join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and pet.is_stop_salary = 0 and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" and is_thirteen = 0 ");
        sb.append(" join ").append(TableName.AGENCY_VERIFY).append(" av")
                .append(" on pet.agency_id = av.agency_id and av.is_month_end = 0");
        sb.append(" where ")
                .append(" pio.is_deleted = 0 ") // 过滤掉已删除的用户
                .append(" and pet.agency_id = ? ")
                .append(" and av.salary_verified_in_agency_code = ?");
        paramList.add(agencyId);
        paramList.add(SystemDictConstant.IS_SUBMIT);
        if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
            sb.append(" and pio.mof_div_code=?");
            paramList.add(queryParam.get("mofDivCode"));
        }
        concatReportPageConditionSql(sb, paramList, queryParam);
        sb.append(" GROUP BY smed.id," +
                "    smed.update_time ," +
                "    pio.id ," +
                "    pet.agency_id ," +
                "    ? ," +
                "    nvl(smed.is_thirteen, 0) ," +
                "    pet.dept_id ," +
                "    pio.person_name ," +
                "    pio.iden_no ");
        paramList.add(salaryMonth);
        sb.append(" ) t order by isThirteen, updateTime desc ");
        return super.getJdbcTemplate().queryForMap(sb.toString(), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> selectUnitSalarySubmitReportPage(PaginationDTO paginationDTO, String agencyId, List<String> salaryIds,
                                                                      String salaryMonth, Map<String, String> currentSalaryIdJoinColumnMap,
                                                                      Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select @i:=@i+1 \"no\", ")
                .append("id \"id\",updateTime \"updateTime\",personId \"personId\",agencyId \"agencyId\",currentMonth \"currentMonth\",deptId \"deptId\",personName \"personName\",idenNo \"idenNo\",isThirteen \"isThirteen\" ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(" , ").append(currentColumn).append(" \"").append(currentColumn).append("\"");
                ifNullCurrentStr.append(" , ").append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn);
            }
            sb.append(currentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pio.id personId, pet.agency_id agencyId, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        sb.append(" nvl(smed.is_thirteen, 0) isThirteen, pet.dept_id deptId, pio.person_name personName, pio.iden_no idenNo ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr);
        }
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ");
        sb.append(" left join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and pet.is_stop_salary = 0 and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" and is_thirteen = 0 ");
        sb.append(" join ").append(TableName.AGENCY_VERIFY).append(" av")
                .append(" on pet.agency_id = av.agency_id and av.is_month_end = 0");
        sb.append(" where ")
                .append(" pio.is_deleted = 0 ") // 过滤掉已删除的用户
                .append(" and pet.agency_id = ? ")
                .append(" and av.salary_verified_in_agency_code = ?");
        paramList.add(agencyId);
        paramList.add(SystemDictConstant.IS_SUBMIT);
        if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
            sb.append(" and pio.mof_div_code=?");
            paramList.add(queryParam.get("mofDivCode"));
        }
//        paramList.add(agencyId);
        concatReportPageConditionSql(sb, paramList, queryParam);
//        拼接第十三月工资
//        sb.append(" union all ");
//        concatReportThirteenSalary(sb, paramList, agencyId, salaryIds,
//                currentSalaryIdJoinColumnMap, queryParam);

        sb.append(" ) t, (select   @i:=0) v order by isThirteen, updateTime desc ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int selectUnitSalarySubmitReportTotal(String agencyId, String lastSalaryMonth, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ").append(" count(*) ")
                .append(" from (");
        sb.append(" select smed.person_id id ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" on ").append(" pio.id = smed.person_id and pio.is_deleted = 0 ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" join ").append(TableName.AGENCY_VERIFY).append(" av")
                .append(" on pet.agency_id = av.agency_id and av.is_month_end = 0");
        sb.append(" where ").append(" smed.agency_id = ? and smed.is_thirteen = 0 ")
                .append(" and av.salary_verified_in_agency_code = ?");
        paramList.add(agencyId);
        paramList.add(SystemDictConstant.IS_SUBMIT);
        if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
            sb.append(" and pio.mof_div_code=?");
            paramList.add(queryParam.get("mofDivCode"));
        }
        concatReportPageConditionSql(sb, paramList, queryParam);
        sb.append(" ) t ");
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    @Override
    public int selectHomeUnitSalaryReportTotal(String agencyId, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ").append(" count(*) ")
                .append(" from (");
        sb.append(" select smed.person_id id ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" on ").append(" pio.id = smed.person_id and pio.is_deleted = 0 ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" join ").append(TableName.AGENCY_VERIFY).append(" av")
                .append(" on pet.agency_id = av.agency_id and av.is_month_end = 0");
        sb.append(" where ").append(" smed.agency_id = ? and smed.is_thirteen = 0 ")
                .append(" and av.salary_verified_in_agency_code = ?")
                .append(" and (av.is_salary_submit = '0' or av.is_salary_submit = '-9' or av.is_salary_submit = '-1')");
        paramList.add(agencyId);
        paramList.add(SystemDictConstant.IS_SUBMIT);
        sb.append(" ) t ");
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }


    private void concatReportThirteenSalary(StringBuilder sb,
                                      List<Object> paramList,
                                      String agencyId,
                                      List<String> salaryIds,
                                      Map<String, String> currentSalaryIdJoinColumnMap,
                                      Map<String, Object> condition){
        sb.append(" select ")
                .append(" smed.id, smed.update_time \"updateTime\", pio.id \"personId\", pet.agency_id \"agencyId\", ")
                .append(" '13月' \"currentMonth\", nvl(smed.is_thirteen, 0) \"isThirteen\", ")
                .append(" pet.dept_id \"deptId\", pio.person_name \"personName\", pio.iden_no \"idenNo\" ");
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        for(String salaryId : salaryIds){
            String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
            ifNullCurrentStr.append(" , nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn);
        }
        sb.append(ifNullCurrentStr).append(ifNullLastStr);
        sb.append(" from ")
                .append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" on ").append(" pio.id = smed.person_id and pio.is_deleted = 0 ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" join ").append(TableName.AGENCY_VERIFY).append(" av")
                .append(" on pet.agency_id = av.agency_id and av.is_month_end = 0");
        sb.append(" where ").append(" smed.agency_id = ? and smed.is_thirteen = 1 ");
        paramList.add(agencyId);
        concatReportPageConditionSql(sb, paramList, condition);
    }

    private void concatReportPageConditionSql(StringBuilder sql, List<Object> paramList, Map<String, Object> queryParam) {
        if (!CollectionUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                if ("0".equals(queryParam.get("flag"))) {
                    // 未上报和撤销
                    sql.append(" and (av.is_salary_submit = '0' or av.is_salary_submit = '-9')");
                }
            }
            // 变动情况
            if (!StringUtils.isEmpty(queryParam.get("changes"))) {
                if(queryParam.get("changes").equals("0")){
                    sql.append(" and av.is_salary_change = 0 ");
                } else {
                    sql.append(" and av.is_salary_change != 0 ");
                }
            }
            // 工资类型
            if (!StringUtils.isEmpty(queryParam.get("salaryType"))) {
                sql.append(" and pet.merit_pay_code = ? ");
                paramList.add(queryParam.get("salaryType"));
            }
            if (!StringUtils.isEmpty(queryParam.get("personType"))) {
                sql.append(" and pio.state = ? ");
                paramList.add(queryParam.get("personType"));
            }
            if (!StringUtils.isEmpty(queryParam.get("personName"))) {
                sql.append(" and pio.person_name like ? ");
                paramList.add('%'+ queryParam.get("personName").toString()+'%');
                //paramList.add(queryParam.get("personName"));
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and pio.iden_no like ? ");
                paramList.add('%'+ queryParam.get("idenNo").toString()+'%');
                //paramList.add(queryParam.get("idenNo"));
            }
        }
    }


    @Override
    public void physicsDeleteAgencySalary(Map<String, Object> queryParam) {
        StringBuilder sql = new StringBuilder();

        sql.append(" delete from ").append(TableName.AGENCY_SALARY);
        sql.append(" where 1 = 1");
        if (!StringUtils.isEmpty(queryParam) && queryParam.size() > 0) {
            // 包含单位信息，根据单位id删除
            if (queryParam.containsKey("agencyId")) {
                String agencyId = queryParam.get("agencyId").toString();
                sql.append(" and agency_id = '").append(agencyId).append("'");
            }
            // 包含工资信息，根据工资id删除
            if (queryParam.containsKey("salaryId")) {
                String salaryId = queryParam.get("salaryId").toString();
                sql.append(" and salary_id = '").append(salaryId).append("'");
            }
        }
        this.getJdbcTemplate().update(sql.toString());
    }

    @Override
    public Map<String, Object> selectSalaryMonthEndSum(String agencyId,
                                                       Integer queryThirteen,
                                                       List<String> queryColumn,
                                                       Map<String, Object> condition,String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ");
        for (String name : queryColumn) {
            sb.append(" nvl(sum(").append(name).append("), 0.00) \"").append(name).append("\"");
            if (!(queryColumn.indexOf(name) == queryColumn.size() - 1)) {
                sb.append(", ");
            }
        }
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" AND pet.is_stop_salary = 0 ")
                .append(" AND pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and smed.agency_id = ? ");
        paramList.add(agencyId);
        if(SystemDictConstant.IS_FALSE.equals(queryThirteen)){
            sb.append("  and smed.is_thirteen = 0 ");
        }

        sb.append(" where ").append(" pio.is_deleted = 0  "); // 过滤掉已删除的用户
        if(!StringUtils.isEmpty(mofDivCode)){
            sb.append(" and pio.mof_div_code= ? ");
            paramList.add(mofDivCode);
        }

        concatUnitAuditPageConditionSql(sb, paramList, condition);
        if(!StringUtils.isEmpty(condition.get("salaCardBank"))){
            sb.append(" and pet.sala_card_bank = ?");
            paramList.add(condition.get("salaCardBank"));
        }
        return super.getjdbcTemplate().queryForMap(sb.toString(), paramList.toArray());
    }

    @Override
    public List<Map<String, Object>> selectSalaryName(String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" sba.salary_name \"key\", asy.column_name \"value\" , is_compute \"iscompute\"")
                .append(" from ")
                .append(TableName.AGENCY_SALARY).append(" asy ")
                .append(" left join ").append(TableName.SALARY_BASEDATA).append(" sba ")
                .append(" on ").append(" asy.salary_id = sba.id ")
                .append(" where ")
                .append(" sba.is_deleted = 0 and sba.base_state = '1' ")
                .append(" and asy.is_deleted = 0 and asy.agency_id =  ? ")
                .append(" order by sba.show_order ");
        return getjdbcTemplate().queryForList(sb.toString(), agencyId);
    }

    @Override
    public List<AgencySalaryVO> selectColumnName(UserDTO userDTO) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" sba.id \"salaryId\", ")
                .append(" sba.salary_code \"columnName\", ")
                .append(" sba.is_compute \"isCompute\", ")
                .append(" sba.salary_name \"salaryName\"")
                .append(" from ")
//                .append(TableName.AGENCY_SALARY).append(" asy ")
//                .append(" left join ")
                .append(TableName.SALARY_BASEDATA).append(" sba ")
//                .append(" on ").append(" asy.salary_id = sba.id ")
                .append(" where ")
                .append(" sba.is_deleted = 0 and sba.base_state = '1' ")
                .append(" and  sba.mof_div_code=? ")
//                .append(" and asy.is_deleted = 0 and asy.agency_id = ? ")
                .append(" order by sba.show_order ");
        return getjdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(AgencySalaryVO.class),userDTO.getProvince());
    }

    @Override
    public List<AgencySalaryVO> selectColumnName(UserDTO userDTO, List<String> salaryCodeList) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" select ")
                .append(" sba.salary_code, sba.salary_name,")
                .append(" sba.salary_code \"columnName\" ")
//                .append(" nvl(asy.column_name, 0) columnName ")
                .append(" from ").append(TableName.SALARY_BASEDATA).append(" sba ");
//                .append(" left join ").append(TableName.AGENCY_SALARY).append(" asy ")
//                .append(" on ").append(" asy.salary_id = sba.id and asy.is_deleted = 0 and asy.agency_id = ? ");
//        params.add(agencyId);
        sb.append(" where ").append(" sba.is_deleted = 0 and sba.base_state = '1' ").append(" and  sba.mof_div_code=? ");
        params.add(userDTO.getProvince());
        StringBuilder inSql = new StringBuilder();
        StringBuilder orderSql = new StringBuilder();
        if(!CollectionUtils.isEmpty(salaryCodeList)){
            sb.append(" and ").append(SysUtils.inSql("sba.salary_code",salaryCodeList));
            sb.append(" order by sba.show_order ");
//            for(String salaryCode : salaryCodeList){
//                if(salaryCodeList.indexOf(salaryCode) != salaryCodeList.size() - 1){
//                    inSql.append(" ?, ");
//                    orderSql.append(" ?, ");
//                }else{
//                    inSql.append(" ?) ");
//                    orderSql.append(" ?) ");
//                }
//            }
//            sb.append(inSql);
//            params.addAll(salaryCodeList);
//            sb.append(orderSql);
//            params.addAll(salaryCodeList);
        }
        return super.getjdbcTemplate().query(sb.toString(),params.toArray(), new BeanPropertyRowMapper<>(AgencySalaryVO.class));
    }


    @Override
    public List<AgencySalaryVO> selectLastColumnName(String agencyId, List<String> salaryIds, String lastSalaryMonth) {
        if (StringUtils.isEmpty(agencyId) || CollectionUtils.isEmpty(salaryIds)) {
            return Collections.emptyList();
        }
        StringBuilder sb = new StringBuilder();
        List<String> params = new ArrayList<>();
        sb.append(" select ")
                .append(" salary_id, column_name ")
                .append(" from ")
                .append(TableName.AGENCY_SALARY_HISTORY)
                .append(" where ")
                .append(" is_deleted = 0 ")
                .append(" and salary_month = ? ")
                .append(" and agency_id = ? ");
        params.add(lastSalaryMonth);
        params.add(agencyId);
        sb.append(" and salary_id in(");
        for (String salaryId : salaryIds) {
            sb.append(" ? ");
            params.add(salaryId);
            if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                sb.append(" ) ");
            } else {
                sb.append(" , ");
            }

        }
        return getjdbcTemplate().query(sb.toString(), params.toArray(), new BeanPropertyRowMapper<>(AgencySalaryVO.class));
    }

    @Override
    public String selectReallySendSalaryColumnName(String agencyId) {
        String reallySendSalaryId = "46F1370C5F2A4D31AD196718A93DC470";
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" salary_code ")
                .append(" from ")
                .append(TableName.SALARY_BASEDATA)
                .append(" where ")
                .append(" is_deleted = 0  and salary_id = ? ");
        try {
            return super.getjdbcTemplate().queryForObject(sb.toString(), String.class, reallySendSalaryId);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<AgencySalary> getInfoByAgencyIds(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id, create_time, update_time, agency_id, salary_id, ")
                .append(" is_accsalwithhold, column_name, is_deleted from ").append(TableName.AGENCY_SALARY);
        sql.append(" where ").append(SysUtils.inSql("agency_id", agencyIds));
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(AgencySalary.class));
    }

    @Override
    public void batchSaveAgencySalaryHistory(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.AGENCY_SALARY_HISTORY)
                .append(" ( id, create_time, update_time, salary_month, agency_id, salary_id, is_accsalwithhold, ")
                .append(" column_name, is_deleted,mof_div_code )")
                .append(" values (")
                .append("?,?,?,?,?,?,?,?,?,?)");
        this.getJdbcTemplate().batchUpdate(sql.toString(), params);
    }

    @Override
    public void deleteAgencySalaryHistory(String month, List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ").append(TableName.AGENCY_SALARY_HISTORY);
        sql.append(" WHERE salary_month = ? and ").append(SysUtils.inSql("agency_id", agencyIds));
        this.getJdbcTemplate().update(sql.toString(), new Object[]{month});
    }

    @Override
    public List<SalaryMonthEnd> getSalaryMonthByPersonId(List<String> columnName, String agencyId, String personId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id");
        for (String column : columnName) {
            sql.append(" ,").append(column);
        }
        sql.append(" from ").append(TableName.SALARY_MONTH_END_DOUBLE);
        sql.append(" where person_id = ?");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryMonthEnd.class), new Object[]{personId});
    }

    @Override
    public List<SalaryMonthEnd> getSalaryMonthEedSubmitByPersonId(List<String> columnName, String agencyId, String personId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select id");
        for (String column : columnName) {
            sql.append(" ,").append(column);
        }
        sql.append(" from ").append(TableName.SALARY_MONTH_END_SUBMIT);
        sql.append(" where person_id = ?");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryMonthEnd.class), new Object[]{personId});
    }

    @Override
    public List<Map<String, Object>> getSelectUnitSalaryReport(String agencyId, List<String> salaryIds, String salaryMonth, String lastSalaryMonth, Map<String, String> currentSalaryIdJoinColumnMap, Map<String, String> lastSalaryIdJoinColumnMap, Map<String, Object> condition) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append("id \"id\",updateTime \"updateTime\",personId \"personId\",agencyId \"agencyId\",currentMonth \"currentMonth\",currentMonth_history \"currentMonth_history\",deptId \"deptId\",personName \"personName\",idenNo \"idenNo\", ");
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder currentStr;
        StringBuilder lastStr;
        if (!CollectionUtils.isEmpty(salaryIds)) {
            currentStr = new StringBuilder();
            lastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            StringBuilder differentStr = new StringBuilder(" (");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                //String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(currentColumn).append(" \"").append(currentColumn).append("\", ");
                lastStr.append(currentColumn).append("_history \"").append(currentColumn).append("_history\", ");
                ifNullCurrentStr.append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn).append(" , ");
//                if (!StringUtils.isEmpty(lastColumn)) {
                    ifNullLastStr.append(" nvl( shy.").append(currentColumn).append(", 0 ) ").append(currentColumn).append("_history, ");
//                } else {
////                    如果上月没有对应的工资类型，则工资默认为零
//                    ifNullLastStr.append(" 0 ").append(currentColumn).append("_history, ");
//                }
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                    differentStr.append(" ) \"different\" ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pio.id personId, pet.agency_id agencyId, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        sb.append(" ? currentMonth_history, ");
        paramList.add(lastSalaryMonth);
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" pet.dept_id deptId, pio.person_name personName, pio.iden_no idenNo ");
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" AND pet.is_stop_salary = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" AND pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and smed.is_thirteen = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        sb.append(" and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_HISTORY).append(" shy ")
                .append(" on ").append(" pio.id = shy.person_id ")
                .append(" and shy.salary_month = ? ");
        paramList.add(lastSalaryMonth);
        sb.append(" and shy.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" where ").append(" pio.is_deleted = ? "); // 过滤掉已删除的用户
        paramList.add(SystemDictConstant.IS_FALSE);
        concatUnitAuditTotalConditionSql(condition, paramList, sb);
        sb.append(" ) t order by \"different\" desc, updateTime desc ");

        return super.getJdbcTemplate().queryForList(sb.toString(), paramList.toArray());


    }


    @Override
    public List<Map<String, Object>> getFinancialAuditSalaryPage(PaginationDTO paginationDTO,
                                                         String agencyId,
                                                         List<String> salaryIds,
                                                         String salaryMonth,
                                                         String lastSalaryMonth,
                                                         Map<String, String> currentSalaryIdJoinColumnMap,
                                                         Map<String, Object> condition,String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder ifNullLastStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" id \"id\",updateTime \"updateTime\",personId \"personId\",agencyId \"agencyId\",currentMonth \"currentMonth\",currentMonth_history \"currentMonth_history\",deptId \"deptId\",personName \"personName\",idenNo \"idenNo\",isThirteen \"isThirteen\" ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            StringBuilder lastStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            ifNullLastStr = new StringBuilder();
            //拼接判断当月和上月工资是否不同sql
            StringBuilder differentStr = new StringBuilder(" , case when ");
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                //String lastColumn = lastSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append(" , ").append(currentColumn).append(" \"").append(currentColumn).append("\" ");
                lastStr.append(" , ").append(currentColumn).append("_history ").append(" \"").append(currentColumn).append("_history\" ");
                ifNullCurrentStr.append(" , ").append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(currentColumn);
                //if (!StringUtils.isEmpty(lastColumn)) {
                ifNullLastStr.append(" , ").append(" nvl( smbn.").append(currentColumn).append(", 0 ) ").append(currentColumn).append("_history ");
                //} else {
//                    如果上月没有对应的工资类型，则工资默认为零
                //  ifNullLastStr.append(" , 0 ").append(currentColumn).append("_history ");
                // }
                differentStr.append(currentColumn).append(" != ").append(currentColumn).append("_history");
                if (salaryIds.indexOf(salaryId) == salaryIds.size() - 1) {
                    differentStr.append(" then 1 else null end \"different\" ");
                } else {
                    differentStr.append(" or ");
                }
            }
            sb.append(currentStr).append(lastStr).append(differentStr);
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pet.person_id personId, pet.agency_id agencyId, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        sb.append(" ? currentMonth_history, nvl(smed.is_thirteen, 0) isThirteen, pet.dept_id deptId, pet.person_name personName, pet.iden_no idenNo ");
        paramList.add(salaryMonth);
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr).append(ifNullLastStr);
        }
        sb.append(" from ")

                .append(TableName.PERSON_EXT_SUBMIT).append(" pet ");

//        sb.append(" left join ").append(TableName.SAL_PERSON_EXT_BEGIN).append(" phy ")
//                .append(" on ").append(" pet.person_id = phy.person_id ")
////                .append(" and phy.is_deleted = 0 ")
////                .append(" and phy.salary_month = ? ")
//                .append("  and phy.agency_id = pet.agency_id ");
//        paramList.add(lastSalaryMonth);
//        paramList.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id  and smed.agency_id = ? ");
        paramList.add(agencyId);
//        sb.append(" and is_thirteen = 0 ")
        sb.append(" left join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pet.person_id = smbn.person_id  and smbn.agency_id = ? ");
        paramList.add(agencyId);
        // 过滤掉已删除的用户
        sb.append(" where ").append(" pet.is_deleted = 0 ");
        if(!StringUtils.isEmpty(mofDivCode)){
            sb.append(" and pet.mof_div_code= ?");
            paramList.add(mofDivCode);
        }
        sb.append("  and pet.agency_id = ? ");
        paramList.add(agencyId);
        concatUnitAuditPageConditionSql(sb, paramList, condition);
//        拼接第十三月工资
//        sb.append(" union all ");
//        concatThirteenSalary(sb, paramList, agencyId, salaryIds,
//                currentSalaryIdJoinColumnMap, condition,mofDivCode);

        sb.append(" ) t order by  \"different\" desc, updateTime desc ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int selectFinancialAuditSalaryTotal(String agencyId, String lastSalaryMonth, Map<String, Object> condition,String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select count(*) ")
                .append(" from ")
                .append(TableName.PERSON_EXT_SUBMIT).append(" pet ");
        // 过滤掉已删除的用户
        sb.append(" where ").append(" pet.is_deleted = 0 and pet.is_deleted = 0 and pet.agency_id = ?  ");
        paramList.add(agencyId);
        if(!StringUtils.isEmpty(mofDivCode)){
            sb.append(" and pet.mof_div_code=?");
            paramList.add(mofDivCode);
        }
        concatUnitAuditTotalConditionSql(condition, paramList, sb);
//        sb.append(" union all ");
//        sb.append(" select smed.person_id id ")
//                .append(" from ")
//                .append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
//                .append(" join ").append(TableName.PERSON_INFO).append(" pio ")
//                .append(" on ").append(" pio.id = smed.person_id and pio.is_deleted = 0 ")
//                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
//                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
//                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
//        paramList.add(agencyId);
//        sb.append(" where ").append(" smed.agency_id = ? ");
//        paramList.add(agencyId);
//        if(!StringUtils.isEmpty(mofDivCode)){
//            sb.append(" and pio.mof_div_code=?");
//            paramList.add(mofDivCode);
//        }
//        concatUnitAuditTotalConditionSql(condition, paramList, sb);
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }


    @Override
    public Map<String, Object> selectSalaryMonthEndSubmSum(String agencyId,
                                                       Integer queryThirteen,
                                                       List<String> queryColumn,
                                                       Map<String, Object> condition,String mofDivCode) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ");
        for (String name : queryColumn) {
            sb.append(" nvl(sum(").append(name).append("), 0.00) \"").append(name).append("\"");
            if (!(queryColumn.indexOf(name) == queryColumn.size() - 1)) {
                sb.append(", ");
            }
        }
        sb.append(" from ")

                .append(TableName.PERSON_EXT_SUBMIT).append(" pet ");

        sb.append(" left join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id and smed.agency_id = ? ");
        paramList.add(agencyId);
        if(SystemDictConstant.IS_FALSE.equals(queryThirteen)){
            sb.append("  and smed.is_thirteen = 0 ");
        }

        sb.append(" where ").append(" pet.is_deleted = 0  "); // 过滤掉已删除的用户
        if(!StringUtils.isEmpty(mofDivCode)){
            sb.append(" and pet.mof_div_code= ? ");
            paramList.add(mofDivCode);
        }
            sb.append(" AND pet.agency_id = ? ");
        paramList.add(agencyId);
        concatUnitAuditPageConditionSql(sb, paramList, condition);
        if(!StringUtils.isEmpty(condition.get("salaCardBank"))){
            sb.append(" and pet.sala_card_bank = ?");
            paramList.add(condition.get("salaCardBank"));
        }
        return super.getjdbcTemplate().queryForMap(sb.toString(), paramList.toArray());
    }
    @Override
    public List<SalaryMonthEndVO> selectSalaryMonthEndWithSalaryType(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code,is_thirteen, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100, ")
                .append(" s101,s102,s103,s104,s105,s106,s107,s108,s109,s110,s111,s112,s113,s114,s115,s116,s117,s118,s119,s120, ")
                .append(" s121,s122,s123,s124,s125,s126,s127,s128,s129,s130,s131,s132,s133,s134,s135,s136,s137,s138,s139,s140, ")
                .append(" s141,s142,s143,s144,s145,s146,s147,s148,s149,s150 ")
                .append(" from ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? ");
        paramList.add(province);
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("smed.agency_id", agencyIds));
        }
        try{
            return super.getJdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class));
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyList();
        }
    }

    @Override
    public void insertSalaryMonthEndDao(SalaryMonthEnd data) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        LocalDateTime localDateTime = LocalDateTime.now();
        List<Object> params = new ArrayList<Object>() {{
            add(SysUtils.generateGuid());
            add(localDateTime.format(dtf));
            add(localDateTime.format(dtf));
            add(data.getAgencyId());
            add(data.getPersonId());
            add(NO_THIRTEEN);
            add(data.getMofDivCode());
        }};
        StringBuilder sb = new StringBuilder();
        sb.append(" insert into ")
                .append(TableName.SALARY_MONTH_END_DOUBLE)
                .append(" (id, create_time, update_time, agency_id, person_id, is_thirteen,mof_div_code ");

        String columnNames = data.getColumnNames();
        if (!StringUtils.isEmpty(columnNames)) {
            String[] columnNameArr = columnNames.split(",");
            List<String> colunmNameList = Arrays.stream(columnNameArr)
                    .map(name -> name.split("_")[0])
                    .collect(Collectors.toList());
            for (String columnName : colunmNameList) {
                sb.append(" , ").append(columnName);
            }
        }
        sb.append(" ) ").append(" values").append("(?,?,?,?,?,?,?");

        if (!StringUtils.isEmpty(columnNames)) {
            String[] columnNameArr = columnNames.split(",");
            List<String> colunmNameList = Arrays.stream(columnNameArr)
                    .map(name -> name.split("_")[0])
                    .collect(Collectors.toList());
            for (String columnName : colunmNameList) {
                sb.append(",?");
                Class<?> clazz = data.getClass();
                Method getMethod = clazz.getDeclaredMethod("get" +
                        columnName.substring(0, 1).toUpperCase() + columnName.substring(1));
                params.add(getMethod.invoke(data));
            }
        }
        sb.append(" ) ");
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public void updateSalaryMonthEndDao(SalaryMonthEnd data) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        LocalDateTime localDateTime = LocalDateTime.now();
        List<Object> params = new ArrayList<Object>() {{
            add(localDateTime.format(dtf));
            add(data.getAgencyId());
            // add(data.getPersonId());
        }};
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_MONTH_END_DOUBLE)
                .append(" set ")
                .append(" update_time = ?, agency_id = ? ");
        String columnNames = data.getColumnNames();
        if (!StringUtils.isEmpty(columnNames)) {
            String[] columnNameArr = columnNames.split(",");
            List<String> colunmNameList = Arrays.stream(columnNameArr)
                    .map(name -> name.split("_")[0])
                    .collect(Collectors.toList());
            for (String columnName : colunmNameList) {
                sb.append(" ,").append(columnName).append(" = ? ");
                Class<?> clazz = data.getClass();
                Method getMethod = clazz.getDeclaredMethod("get" +
                        columnName.substring(0, 1).toUpperCase() + columnName.substring(1));
                params.add(getMethod.invoke(data));
            }
        }
        sb.append(" where ");
        if (!StringUtils.isEmpty(data.getId())) {
            sb.append("id = ? ");
            params.add(data.getId());
        } else if (!StringUtils.isEmpty(data.getPersonId())) {
            sb.append(" person_id = ? ");
            params.add(data.getPersonId());
        }
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public String selectSubmitSalarySum(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" sum(").append(queryParam.get("salaryCloumnName")).append(") ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_END_DOUBLE).append(" a")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" b")
                .append(" on a.agency_id = b.agency_id")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio")
                .append(" on a.person_id = pio.id")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet")
                .append(" on pio.id = pet.person_id")
                .append(" where ")
                .append(" a.agency_id = '").append(queryParam.get("agencyId")).append("'")
                .append(" and pet.is_stop_salary = 0 ")
                .append(" and b.is_month_end = 0 and b.is_thirteen = 0  and b.salary_verified_in_agency_code = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                if ("0".equals(queryParam.get("flag"))) {
                    // 未上报和撤销
                    sql.append(" and (b.is_salary_submit = '0' or b.is_salary_submit = '-9')");
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("personType"))) {
                sql.append(" and pio.state = ? ");
                paramList.add(queryParam.get("personType"));
            }
            if (!StringUtils.isEmpty(queryParam.get("personName"))) {
                sql.append(" and pio.person_name like ?");
                paramList.add('%'+queryParam.get("personName").toString()+'%');
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and pio.iden_no like ? ");
                paramList.add('%'+queryParam.get("idenNo").toString()+'%');
            }
        }
        return super.getjdbcTemplate().queryForObject(sql.toString(), String.class, paramList.toArray());
    }
    @Override
    public void batchUpdateSalaryMonthEnd(List<SalaryMonthEndVO> salaryMonthEndVOs) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        if(!CollectionUtils.isEmpty(salaryMonthEndVOs)){
            List<Object[]> paramList = new ArrayList<>(salaryMonthEndVOs.size());
            Class<?> clazz = SalaryMonthEndVO.class;
            for(SalaryMonthEndVO salaryMonthEndVO : salaryMonthEndVOs){
                List<Object> params = new ArrayList<>();
                params.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
                for(int i = 1; i <= 150; i++){
                    Method getMethod = clazz.getDeclaredMethod("getS"+i);
                    params.add(getMethod.invoke(salaryMonthEndVO));
                }
                params.add(salaryMonthEndVO.getAgencyId());
                params.add(salaryMonthEndVO.getPersonId());
                paramList.add(params.toArray());
            }
            StringBuilder sb = new StringBuilder();
            sb.append(" update ").append(TableName.SALARY_MONTH_END_DOUBLE)
                    .append(" set ").append(" update_time = ? ");
            for(int i = 1; i <= 150; i++){
                sb.append(" ,s").append(i).append(" = ? ");
            }
            sb.append(" where ").append(" agency_id = ? and person_id = ? ");
            super.getjdbcTemplate().batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
                public int getBatchSize() {
                    return paramList.size();
                    //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
                }
                public void setValues(PreparedStatement ps, int i)throws SQLException {
                    Object[] sme = paramList.get(i);
                    for(int a = 0; a <= 151; a++){
                        ps.setObject(a+1,sme[a]);
                    }

                }
            });
        }
    }


    @Override
    public List<String> selectDifferentPersonId(String agencyId) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select id from ( ")
                .append(" select ").append(" pio.id ");
        StringBuilder whereStr = new StringBuilder(" s2 = 0 ");
        for(int i = 1; i <= 150; i++){
            sb.append(" ,nvl(smed.s").append(i).append(", 0) s").append(i);
            sb.append(" ,nvl(smbn.s").append(i).append(", 0) s").append(i).append("_history ");
            whereStr.append(" or s").append(i).append(" != ").append("s").append(i).append("_history ");
        }
        sb.append(" from ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append("  join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left  join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pio.id = smbn.person_id and smbn.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" where ").append(" pio.is_deleted = 0 ");
        sb.append(" ) t ").append(" where ").append(whereStr.toString());
        return super.getjdbcTemplate().queryForList(sb.toString(), paramList.toArray(), String.class);
    }




    @Override
    public UnitSalaryReportVO selectReallySendSalarySum(String agencyId, String isThirteen, String reallySendSalaryName, String salaryType) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                //如果sum(reallySendSalaryName)为空则把0.00赋值给 sum
                .append(" nvl(sum(").append(reallySendSalaryName).append("), 0.00) reallySendSalarySum, ")
                .append(" nvl(sum(case pio.state when '3' then ").append(reallySendSalaryName)
                .append(" else 0 end ), 0.00) workSalarySum, ")
                .append(" nvl(sum(case pio.state when '1' then ").append(reallySendSalaryName)
                .append(" else 0 end ), 0.00) retireSalarySum ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        params.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_END_DOUBLE).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and smed.agency_id = ? ");
        params.add(agencyId);

        sb.append(" and smed.is_thirteen = ? ");
        params.add(isThirteen);

        sb.append(" where ").append(" pio.is_deleted = 0 ");
        if (!StringUtils.isEmpty(salaryType)) {
            sb.append(" and pet.merit_pay_code = ?");
            params.add(salaryType);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(UnitSalaryReportVO.class), params.toArray());
    }

//    @Override
//    public void updateRealAndWagesAndSub(String agencyId,List<String> addlist,List<String> sublist) {
//        StringBuilder sb = new StringBuilder();
//        sb.append(" update ")
//                .append(TableName.SALARY_MONTH_END)
//                .append(" set s1=");
//        StringBuilder addStr= new StringBuilder();
//        StringBuilder subStr= new StringBuilder();
//        addStr.append("0");
//        for(String add:addlist){
//            addStr.append("+")
//                    .append("nvl(")
//                    .append(add)
//                    .append(",0)");
//        }
//        subStr.append("0");
//        for(String sub:sublist){
//            subStr.append("-")
//                    .append("nvl(")
//                    .append(sub)
//                    .append(",0)");
//        }
//        sb.append(addStr)
//                .append("-")
//                .append(subStr);
//        sb.append(",s2=")
//                .append(addStr);
//
//        if(null!=agencyId){
//            sb.append(" where agency_id=?");
//            Object[] params = new Object[]{
//                    agencyId
//            };
//            super.getjdbcTemplate().update(sb.toString(),params);
//        }else {
//            super.getjdbcTemplate().update(sb.toString());
//        }
//    }

    @Override
    public  List<AgencyVerify> getAgencyVerifyByAgencyId(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT  ");
        sql.append(" id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code,");
        sql.append(" salary_info_verified_code, person_info_verified_code, agency_info_verified_code,");
        sql.append(" is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit,");
        sql.append(" is_month_end, is_deleted,audit_explain, remark, is_salary_change, add_person_num, delete_person_num, is_thirteen,");
        sql.append(" is_pay_data_created, is_bank_data_created,mof_div_code,year,is_auto,is_verify_end,agency_code,agency_name,payment_create_time ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where  agency_id = ?  ")
                .append(" and salary_month = (SELECT MAX (salary_month) month FROM  sal_agency_verify  where agency_id = ? and is_month_end='1')");
        Object[] objects = new Object[]{
                agencyId ,agencyId
        };
            return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class), objects);

    }

    @Override
    public AgencyVerify getThirteen(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append("  MAX (is_thirteen) is_thirteen ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where  agency_id = ? and is_month_end='1' ");
        Object[] objects = new Object[]{
                 agencyId
        };
        try {
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class),objects);
        } catch (EmptyResultDataAccessException e) {
            return  new AgencyVerify();
        }
    }

    @Override
    public AgencyVerify getIsThirteen(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append("  MAX (is_thirteen) is_thirteen ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where  agency_id = ? and is_month_end='0' ");
        Object[] objects = new Object[]{
                agencyId
        };
        try {
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class),objects);
        } catch (EmptyResultDataAccessException e) {
            return  new AgencyVerify();
        }
    }

//    @Override
//    public void insetAgencyVerify(AgencyVerify agencyVerify) {
//        StringBuilder sql = new StringBuilder();
//        sql.append("insert into ").append(TableName.AGENCY_VERIFY);
//        sql.append("(id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code, ")
//                .append("salary_info_verified_code, person_info_verified_code, agency_info_verified_code, ")
//                .append("is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, ")
//                .append("is_confirm_submit, is_month_end, is_deleted, is_salary_change, add_person_num, delete_person_num, is_thirteen, is_auto");
//        sql.append("values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
//        Object[] params = new Object[]{
//                agencyVerify.getId(), agencyVerify.getBizKey(), agencyVerify.getCreateTime(), agencyVerify.getUpdateTime(),
//                agencyVerify.getAgencyId(), agencyVerify.getSalaryMonth(), agencyVerify.getSalaryVerifiedInAgencyCode(),
//                agencyVerify.getSalaryInfoVerifiedCode(), agencyVerify.getPersonInfoVerifiedCode(),
//                agencyVerify.getAgencyInfoVerifiedCode(), agencyVerify.getIsSubmitInAgency(), agencyVerify.getIsSalarySubmit(),
//                agencyVerify.getIsPersonSubmit(), agencyVerify.getIsAgencySubmit(), agencyVerify.getIsConfirmSubmit(),
//                agencyVerify.getIsMonthEnd(), agencyVerify.getIsDeleted(), agencyVerify.getIsSalaryChange(),
//                agencyVerify.getAddPersonNum(), agencyVerify.getDeletePersonNum(), agencyVerify.getIsThirteen(),SystemDictConstant.IS_FALSE
//        };
//        this.getJdbcTemplate().update(sql.toString(), params);
//    }

    @Override
    public void insetAgencyVerify(AgencyVerify agencyVerify) {
    StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.AGENCY_VERIFY);
        sql.append("( id, create_time, agency_id," )
                .append(  " salary_verified_in_agency_code, salary_info_verified_code, person_info_verified_code, agency_info_verified_code, is_submit_in_agency," )
                .append(  " is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit, is_deleted, " )
                .append( "audit_explain, remark, is_salary_change, add_person_num, add_quit_person_num, " )
                .append("delete_person_num, is_thirteen, is_pay_data_created, is_bank_data_created, mof_div_code, " )
                .append( "year, is_auto, is_verify_end, agency_code, agency_name,salary_month,is_month_end,biz_key)")
                .append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        Object[] params = new Object[]{
                agencyVerify.getId(),agencyVerify.getCreateTime(),agencyVerify.getAgencyId(),
                agencyVerify.getSalaryVerifiedInAgencyCode(),agencyVerify.getSalaryInfoVerifiedCode(),agencyVerify.getPersonInfoVerifiedCode(),agencyVerify.getAgencyInfoVerifiedCode(),agencyVerify.getIsSubmitInAgency(),
                agencyVerify.getIsSalarySubmit(),agencyVerify.getIsPersonSubmit(),agencyVerify.getIsAgencySubmit(),agencyVerify.getIsConfirmSubmit(),agencyVerify.getIsDeleted(),
                agencyVerify.getAuditExplain(),agencyVerify.getRemark(),agencyVerify.getIsSalaryChange(),agencyVerify.getAddPersonNum(),agencyVerify.getAddQuitPersonNum(),
                agencyVerify.getDeletePersonNum(),agencyVerify.getIsThirteen(),agencyVerify.getIsPayDataCreated(),agencyVerify.getIsBankDataCreated(),agencyVerify.getMofDivCode(),
                agencyVerify.getYear(),agencyVerify.getIsAuto(),agencyVerify.getIsVerifyEnd(),agencyVerify.getAgencyCode(),agencyVerify.getAgencyName(),agencyVerify.getSalaryMonth(),agencyVerify.getIsMonthEnd(),agencyVerify.getBizKey()
        };
        this.getJdbcTemplate().update(sql.toString(), params);

    }


    @Override
    public List<SalaryHistory> getHistoryList(String agencyId,String mofDivCode) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT  DISTINCT");
        sql.append(" person_id, agency_id,mof_div_code  FROM ").append(TableName.SALARY_HISTORY);
        sql.append(" where agency_id=? and is_thirteen=0 and mof_div_code=? ");
        Object[] objects = new Object[]{
                agencyId ,mofDivCode
        };
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryHistory.class), objects);
    }

    @Override
    public void insetAgencyMonthEndDouble(List<SalaryHistory> historyList, Integer thirteen) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SALARY_MONTH_END_DOUBLE);
        sql.append(" (id, create_time, update_time, agency_id, person_id, is_thirteen,mof_div_code) ");
        sql.append(" values (?, ?, ?, ?, ?, ?,?)");
        List<Object[]> paramsList = new ArrayList<>(historyList.size());
        for (SalaryHistory history : historyList) {
            Object[] params = new Object[]{
                    SysUtils.generateGuid(),DateUtils.getCurrentTimeStamp(),DateUtils.getCurrentTimeStamp(),
                    history.getAgencyId(), history.getPersonId(), thirteen,history.getMofDivCode()
            };
            paramsList.add(params);
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a <= 6; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public AgencyVerify getAgencyVerifyByAgencyIds(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code,");
        sql.append(" salary_info_verified_code, person_info_verified_code, agency_info_verified_code,");
        sql.append(" is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit,");
        sql.append(" is_month_end, is_deleted, remark, is_salary_change, add_person_num, delete_person_num, is_thirteen,");
        sql.append(" audit_explain, is_pay_data_created, is_bank_data_created  ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = ? and agency_id = ?")
           .append(" and is_thirteen=(SELECT MAX (is_thirteen) month FROM  sal_agency_verify where agency_id =? and is_month_end='0') ");
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE, agencyId,agencyId
        };
        try{
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class), objects);
        }catch(EmptyResultDataAccessException e){
            return new AgencyVerify();
        }
    }

    @Override
    public Map<String, Object> selectAgencyNameAndState(String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" avy.agency_name \"agencyName\", ")
                .append(" avy.is_submit_in_agency \"isSubmitInAgency\", ")//单位内部工资是否送审
                .append(" avy.salary_verified_in_agency_code \"salaryVerifiedInAgencyCode\", ")//机构内部工资审核状态
                .append(" avy.is_salary_submit \"isSalarySubmit\" ")//工资信息是否上报
                .append(" from ")
                .append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" where ").append("  avy.agency_id = ?  and avy.is_deleted = 0 and avy.is_month_end = 0")
                .append(" and avy.is_thirteen=(SELECT MAX (is_thirteen) month FROM  sal_agency_verify where agency_id =? and is_month_end='0') ");
        try{
            return super.getjdbcTemplate().queryForMap(sb.toString(), agencyId,agencyId);
        }catch (EmptyResultDataAccessException e){
            return Collections.emptyMap();
        }
    }

    @Override
    public void deletedDouble(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.SALARY_MONTH_END_DOUBLE);
        sql.append(" where agency_id = ? ");
        Object[] objects = new Object[] {
               agencyId
        };
        super.getJdbcTemplate().update(sql.toString(), objects);
    }

    @Override
    public AgencyVerify getAgencyVerifyBizKey(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append("  biz_key, agency_id, salary_month, is_month_end, is_thirteen");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = ? and agency_id = ?")
                .append(" and is_thirteen=(SELECT MAX (is_thirteen) month FROM  sal_agency_verify where agency_id =? and is_month_end='0') ");
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE, agencyId,agencyId
        };
        try{
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class), objects);
        }catch(EmptyResultDataAccessException e){
            return new AgencyVerify();
        }
    }

    @Override
    public String getZeroAbles(String province) {
        try{
            StringBuilder sql = new StringBuilder();
            sql.append(" select ")
                    .append(" is_able ")
                    .append(" from ")
                    .append(TableName.SAL_CONFIG_CTRL)
                    .append(" where config_type='zeroAble' and  mof_div_code = ? ");
            return this.getJdbcTemplate().queryForObject(sql.toString(),String.class,province);
        }catch (Exception e){
            return "1";
        }
    }
}
